package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.GraduateProjectCategory;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.List;
import java.util.TreeSet;

public final class GraduateProjectCategoryDao {
    //本类的一个对象引用，保存自身对象
    private static GraduateProjectCategoryDao graduateProjectCategoryDao = null;
    private static Collection<GraduateProjectCategory> graduateProjectCategories;

//    static {
//        graduateProjectCategories = new TreeSet<GraduateProjectCategory>();
//        GraduateProjectCategory design = new GraduateProjectCategory(1, "设计", "01", "");
//        graduateProjectCategories.add(design);
//        graduateProjectCategories.add(new GraduateProjectCategory(2, "论文", "02", ""));
//    }

    //私有的构造方法，防止其它类创建它的对象
    private GraduateProjectCategoryDao() {
    }

    //静态方法，返回本类的惟一对象
    public synchronized static GraduateProjectCategoryDao getInstance() {
        if (graduateProjectCategoryDao == null){
            graduateProjectCategoryDao = new GraduateProjectCategoryDao();
        }
        return graduateProjectCategoryDao;
    }

    public int count(Connection connection) throws SQLException{
        return CommonDao.getInstance().count(connection,"graduateprojectcategory");
    }

    public Collection<GraduateProjectCategory> findAll(Connection connection,
                                                       Pagination pagination,
                                                       List<Condition> conditions_json)
            throws SQLException {

        Collection<GraduateProjectCategory> graduateProjectCategories = new TreeSet<GraduateProjectCategory>();

        int totalNum = GraduateProjectCategoryDao.getInstance().count(connection);

        //创建sql语句
        StringBuilder findAll_sql = new StringBuilder("select * from graduateprojectcategory");
        //如果有条件，生成子语句，并加在主语句后面
        if (conditions_json!=null){
            String whereClause = Condition.toWhereClause(conditions_json);
            findAll_sql.append(whereClause);
        }
        if (pagination!=null){
            findAll_sql.append(pagination.toLimitClause(totalNum));
        }
        //创建语句盒子对象
        PreparedStatement pstmt = connection.prepareStatement(findAll_sql.toString());
        //将运行结果指向结果集集合
        ResultSet resultSet = pstmt.executeQuery();


        while (resultSet.next()){
            int id = resultSet.getInt("id");
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");

            GraduateProjectCategory findGraduateProjectCategory =
                    new GraduateProjectCategory(id,description,no,remarks);
            graduateProjectCategories.add(findGraduateProjectCategory);
        }
        JdbcHelper.close(resultSet,pstmt);
        return graduateProjectCategories;
    }

    public GraduateProjectCategory find(Integer id, Connection connection) throws SQLException {
        //创建sql语句
        String find_sql = "select * from graduateprojectcategory where id=?";
        //创建连接并在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(find_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        //获取查询结果
        ResultSet resultSet = pstmt.executeQuery();
        resultSet.next();
        //将查询结果赋值给之前创建的变量
        GraduateProjectCategory desiredGraduateProjectCategory =
                new GraduateProjectCategory(
                        id,
                        resultSet.getString("description"),
                        resultSet.getString("no"),
                        resultSet.getString("remarks"));

        JdbcHelper.close(resultSet,pstmt);
        //返回查询结果
        return desiredGraduateProjectCategory;
    }

    public boolean update(GraduateProjectCategory graduateProjectCategory,Connection connection)
            throws SQLException{
        //创建sql语句
        String update_sql = "update graduateprojectcategory set description=?,no=?,remarks=? where id=?";
        //创建连接并在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(update_sql);
        //为预编译参数赋值
        pstmt.setInt(4,graduateProjectCategory.getId());
        pstmt.setString(1,graduateProjectCategory.getDescription());
        pstmt.setString(2,graduateProjectCategory.getNo());
        pstmt.setString(3,graduateProjectCategory.getRemarks());
        //根据执行结果，具体判断所要返回的值
        int affectedRows = pstmt.executeUpdate();
        JdbcHelper.close(pstmt,null);
        return affectedRows>0;
    }

    public boolean add(GraduateProjectCategory graduateProjectCategory,Connection connection)
            throws SQLException {
        //创建sql语句
        String add_sql = "INSERT INTO graduateprojectcategory(description,no,remarks) values"+"(?,?,?)";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(add_sql);
        //为预编译参数赋值
        pstmt.setString(1,graduateProjectCategory.getDescription());
        pstmt.setString(2,graduateProjectCategory.getNo());
        pstmt.setString(3,graduateProjectCategory.getRemarks());
        //获取执行结果
        int affectedRowNum = pstmt.executeUpdate();
        //判断数据库执行结果，返回相应结果
        JdbcHelper.close(pstmt,null);
        return  affectedRowNum>0;
    }

//    public boolean delete(Integer id) {
//
//    }

    public boolean delete(GraduateProjectCategory graduateProjectCategory,Connection connection)
            throws SQLException{
        //创建sql语句
        String delete_sql = "delete from graduateprojectcategory where id=?";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(delete_sql);
        //为预编译参数赋值
        pstmt.setInt(1,graduateProjectCategory.getId());
        //获取执行结果
        int affectedRowNum = pstmt.executeUpdate();
        //判断数据库执行结果，返回相应结果
        JdbcHelper.close(pstmt,null);
        return  affectedRowNum>0;
    }
}
